# -*- coding: UTF-8 -*-
from dbutils.pooled_db import PooledDB
import pymysql
from config.logging_config import logger
from config.template_config  import MysqlConfig
from .snowflake_util import SnowFlake


class MysqlHelper(object):

    def __init__(self, databaseconfig):
        self.pool = PooledDB(
            # 使用链接数据库的模块
            creator=pymysql,
            # 连接池允许的最大连接数，0和None表示不限制连接数
            maxconnections=10,
            # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
            mincached=2,
            # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            blocking=True,
            ping=0,
            # ping MySQL服务端，检查是否服务可用。如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            host=databaseconfig.host,
            port=databaseconfig.port,
            user=databaseconfig.username,
            password=databaseconfig.password,
            database=databaseconfig.database
        )
        self.snowflake = SnowFlake(datacenter_id=1, worker_id=2, sequence=0)

    def open(self):
        # 去连接池中获取一个连接
        conn = self.pool.connection()
        cursor = conn.cursor()
        return conn, cursor

    def close(self, conn, cur):
        cur.close()
        # 将连接放回到连接池，并不会关闭连接，当线程终止时，连接自动关闭
        conn.close()

    def get_columns(self, description):
        column_list = []
        if description is None or len(description) == 0:
            return column_list
        for column in description:
            column_list.append(column[0])
        return column_list

    # 获取所有数据
    # SQL语句的占位参数
    # 查询结果
    def get_list(self, sql, args=None):
        conn, cursor = self.open()
        try:
            cursor.execute(sql, args)
            result = cursor.fetchall()
            column_list = self.get_columns(cursor.description)
            return self.package_data(column_list, result)
        except Exception as e:
            logger.error(e)
        finally:
            self.close(conn, cursor)
            # logger.info("执行成功>>>\n" + sql)

    # 获取单条数据
    # return: 查询结果
    def get_one(self, sql, args=None):
        conn, cursor = self.open()
        try:
            cursor.execute(sql, args)
            column_list = self.get_columns(cursor.description)
            result = cursor.fetchone()
            return self.package_data(column_list, result)
        except Exception as e:
            logger.error(e)
        finally:
            self.close(conn, cursor)
            logger.info("执行成功>>>\n" + sql)

    # 增加数据
    # return: 新增数据行的ID
    def save(self, sql, args=None):
        conn, cursor = self.open()
        try:
            cursor.execute(sql, args)
            conn.commit()
        except Exception as e:
            conn.rollback()
            logger.error(e)
            return 0
        else:
            return 1
        finally:
            self.close(conn, cursor)
            logger.info("执行成功>>>\n" + sql)


    # 批量操作
    def batch_run(self, sql, args=None):
        conn, cursor = self.open()
        try:
            result = cursor.executemany(sql, args)
            conn.commit()
            return result
        except Exception as e:
            conn.rollback()
            logger.error(e)
            return 0
        else:
            return 1
        finally:
            self.close(conn, cursor)
            logger.info("执行成功>>>\n" + sql)

    def execute_sql(self, sql):
        conn, cursor = self.open()
        try:
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            conn.rollback()
            logger.error(e)
            return 0
        else:
            return 1
        finally:
            self.close(conn, cursor)
            logger.info("执行成功>>>\n" + sql)

    def package_data(self, column_list, result):
        data_list = []
        if column_list is None or len(column_list) == 0:
            return data_list
        if result is None or len(result) == 0:
            return data_list
        for data in result:
            data_dict = {}
            for index, item in enumerate(data):
                column = column_list[index].lower()
                data_dict[column] = item
            data_list.append(data_dict)
        return data_list

    def query_column_type(self, table_schema, table_name, column_filter_list=None):
        query_column_sql_template = MysqlConfig.QUERY_COLUMN_TYPE_SQL
        query_column_sql = query_column_sql_template.format(table_schema=table_schema, table_name=table_name)
        result = self.get_list(query_column_sql)
        if result is None or len(result) == 0:
            return result
        column_dict = {}
        if column_filter_list is not None and len(column_filter_list) > 0:
            for item in result:
                column_name = item.get("column_name").lower()
                if column_name in column_filter_list:
                    column_dict[column_name + "_data_type"] = item.get("data_type")
                    column_dict[column_name + "_is_nullable"] = item.get("is_nullable")
                    column_dict[column_name + "_column_default"] = item.get("column_default")
                    column_dict[column_name + "_ordinal_position"] = item.get("ordinal_position")
        else:
            for item in result:
                column_name = item.get("column_name").lower()
                column_dict[column_name + "_data_type"] = item.get("data_type")
                column_dict[column_name + "_is_nullable"] = item.get("is_nullable")
                column_dict[column_name + "_column_default"] = item.get("column_default")
                column_dict[column_name + "_ordinal_position"] = item.get("ordinal_position")

        logger.info("字段类型映射生成成功------------\n" + str(column_dict))
        return column_dict

    def generate_insert_statement(self, table_schema, table_name, column_filter_list=None):
        query_column_sql_template = MysqlConfig.QUERY_COLUMN_SQL
        query_column_sql = query_column_sql_template.format(table_schema=table_schema, table_name=table_name)
        result = self.get_list(query_column_sql)
        if result is None or len(result) == 0:
            return result
        column_list_map = map(lambda item: item["column_name"], result)
        column_list = None
        if column_filter_list is not None and len(column_filter_list) > 0:
            column_list_map_list = list(column_list_map)
            column_list_filter = filter(lambda item: item.lower() in column_filter_list, column_list_map_list)
            column_list = list(column_list_filter)
        else:
            column_list = list(column_list_map)

        placeholders = ', '.join(f'`{column.lower()}`' for column in column_list)
        values = ', '.join("{" + f'{column.lower()}' + "}" for column in column_list)
        sql_template = f"insert into `{table_name}` ({placeholders}) values ({values});"
        format_values = ', '.join(
            f'{column.lower()}' + "= deal_data(item,'" + f'{column.lower()}' + "',column_type_dict)" for column in
            column_list)
        replace_template = "sql_template.format(" + format_values + ")"

        logger.info("语句生成成功---------------\n"+sql_template)
        logger.info("替换模板生成成功------------\n"+replace_template)
        logger.info("-------------------------")
        return sql_template, replace_template
    def generate_batch_insert_statement(self, table_schema, table_name, column_filter_list=None):
        query_column_sql_template = MysqlConfig.QUERY_COLUMN_SQL
        query_column_sql = query_column_sql_template.format(table_schema=table_schema, table_name=table_name)
        result = self.get_list(query_column_sql)
        if result is None or len(result) == 0:
            return result
        column_list_map = map(lambda item: item["column_name"], result)
        column_list = None
        if column_filter_list is not None and len(column_filter_list) > 0:
            column_list_map_list = list(column_list_map)
            column_list_filter = filter(lambda item: item.lower() in column_filter_list, column_list_map_list)
            column_list = list(column_list_filter)
        else:
            column_list = list(column_list_map)

        placeholders = ', '.join(f'`{column.lower()}`' for column in column_list)
        values_template = "( "+', '.join("{" + f'{column.lower()}' + "}" for column in column_list) +" )"
        sql_template = f"insert into `{table_name}` ({placeholders}) values "
        format_values = ', '.join(f'{column.lower()}' + "= deal_data(item,'" + f'{column.lower()}' + "',column_type_dict)" for column  in column_list)
        replace_template = "values_template.format(" + format_values + ")"

        logger.info("语句生成成功---------------\n"+sql_template)
        logger.info("数据模板生成成功---------------\n"+values_template)
        logger.info("替换模板生成成功------------\n"+replace_template)
        logger.info("-------------------------")
        return sql_template,values_template, replace_template
    def generate_params_template_statement(self, table_schema, table_name, column_filter_list=None):
        query_column_sql_template = MysqlConfig.QUERY_COLUMN_SQL
        query_column_sql = query_column_sql_template.format(table_schema=table_schema, table_name=table_name)
        result = self.get_list(query_column_sql)
        if result is None or len(result) == 0:
            return result
        column_list_map = map(lambda item: item["column_name"], result)
        column_list = None
        if column_filter_list is not None and len(column_filter_list) > 0:
            column_list_map_list = list(column_list_map)
            column_list_filter = filter(lambda item: item.lower() in column_filter_list, column_list_map_list)
            column_list = list(column_list_filter)
        else:
            column_list = list(column_list_map)
        column_maps = []
        for column in column_list:
            column_maps.append("data_dict['" + f'{column.lower()}' + "']= item." + f'{column.lower()}')
        params_template = "data_dict = {} \n" + '\n '.join(column_maps)
        logger.info("参数模板生成成功------------\n"+params_template)

    def gen_snowflake_id(self):
        return self.snowflake.get_snowflake_id()
